package otherSAMS.dao;

import otherSAMS.jdbc.DBconnection;
import otherSAMS.model.CourseScore;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class CourseSelectionDao {

    public static  List<CourseScore> selectByStudentName(String studentName) {
        //1.创建连接
        Connection connection = DBconnection.getConnection();
        //2.预编译Sql
        String sql = "SELECT st.studentNo,st.NAME,co.`name`,co.courseCode,co.credit,cs.score,cs.scoreInputTime FROM student AS st LEFT JOIN course_selection AS cs ON cs.studentId = st.id LEFT JOIN course AS co ON co.id = cs.courseId WHERE st.NAME LIKE ?";
        PreparedStatement prepareStatement =null;
        List<CourseScore> list = new ArrayList<>();
        ResultSet resultSet = null;
        try {

            prepareStatement = connection.prepareStatement(sql);
            prepareStatement.setString(1,"%"+studentName+"%");
            resultSet = prepareStatement.executeQuery();
            while (resultSet.next()) {
                int studentNo = resultSet.getInt(1);
                String stuName = resultSet.getString(2);
                String courseName = resultSet.getString(3);
                int courseCode = resultSet.getInt(4);
                int credit = resultSet.getInt(5);
                int score = resultSet.getInt(6);
                Date scoreInputTime = resultSet.getDate(7);
                CourseScore courseScore = new CourseScore(studentNo, stuName, courseName, courseCode,credit, score, scoreInputTime);
                list.add(courseScore);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭连接
            DBconnection.closeConnection(resultSet,prepareStatement,connection);
        }
        return list;
    }

    public int selectByStudentIdAndCourseId(int studentNo,int courseId) {
        //1.创建连接
        Connection connection = DBconnection.getConnection();
        //2.预编译Sql
        String sql = "SELECT\n" +
                "\tcs.* \n" +
                "FROM\n" +
                "\tcourse_selection AS cs\n" +
                "\tLEFT JOIN student AS st ON st.id = cs.studentId\n" +
                "\tLEFT JOIN course AS co ON co.id = cs.courseId \n" +
                "WHERE\n" +
                "\tst.studentNo =? and co.courseCode = ?";
        PreparedStatement prepareStatement = null;
        ResultSet resultSet = null;
        int id = -1;
        try {
            prepareStatement = connection.prepareStatement(sql);
            prepareStatement.setInt(1,studentNo);
            prepareStatement.setInt(2,courseId);
            resultSet = prepareStatement.executeQuery();
            if (resultSet.next()) {
                id = resultSet.getInt(1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭连接
            DBconnection.closeConnection(null,prepareStatement,connection);
        }
        return id;

    }

    public boolean updateScoreById(int score,int id) {
        //1.创建连接
        Connection connection = DBconnection.getConnection();
        //2.预编译Sql
        String sql = "UPDATE course_selection \n" +
                "SET score = ?,scoreInputTime = now()" +
                "WHERE\n" +
                "\tid = ?";
        PreparedStatement prepareStatement = null;
        ResultSet resultSet = null;
        boolean flag = false;
        try {
            prepareStatement = connection.prepareStatement(sql);
            prepareStatement.setInt(1,score);
            prepareStatement.setInt(2,id);
            flag = prepareStatement.executeUpdate()==1;
            resultSet = prepareStatement.executeQuery();
            if (resultSet.next()) {
                id = resultSet.getInt(1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭连接
            DBconnection.closeConnection(null,prepareStatement,connection);
        }
        return flag;
    }
}

